if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetCustomReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetCustomReport]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
/******************************************************************                                          
* Name:   [GetCustomReport]                                          
* Purpose:  Retrieves COIIds matching the filter parameters passed in using multiple table joins         
* Note:     The parameters for selected charts only will be passed in with the common search parameters         
   and the sort parameters.        
*********************************************************************/                                          
CREATE PROCEDURE [dbo].[GetCustomReport]                                              
(                                    
@firstName  VARCHAR(64)=null,                                              
@lastName VARCHAR(64)=null,                                              
@userName VARCHAR(36)=null,
@nothingToDisclose bit=0,                              
@roleXml text=null,                                
@startDate datetime=null,                                    
@endDate datetime=null,     
@outSideEntityName varchar(4000)=null,                                    
@businessNature varchar(4000)=null,                                    
@companyType varchar(4000)=null,                               
@percentageTotalEquityOwned varchar(4000)=null,
                                    
                                 
@chart1ServiceProvided Varchar(4000)=null,                                    
@chart1AnnualEquityCompensation varchar(4000)=null,                                    
@chart1FiduciaryPosition varchar(4000)=null,                                    
@chart1ValueOfEquityOwned varchar(4000)=null,                                    
@chart1WrittenAgreement varchar(4000)=null,                                    
@chart1DaysOfOutsideActivity int=null,                                    
@chart1IsAggregate bit=null,                                    
@chart1AnnualCashCompensation varchar(4000)=null,
@chart1CSRLReview varchar(max) = null,                                
                              
@chart2ValueOfEquityInterest varchar(4000)=null,                              
                              
@chart3FamilyMember varchar(4000)=null,                                  
@chart3FamilyMemberAssociation varchar(4000)=null,                              
@chart3ValueOfEquityInterest varchar(4000)=null, 

@chart4isPayedBypartners varchar(4000) = null,
@chart4paymentNature varchar(4000) = null,
@chart4paymentPurpose varchar(4000) = null,
@chart4annualPayment varchar(4000) = null,
                              
@trusteeNonPartnersAssociationChart1 varchar(4000)=null,                              
@trusteeDescOfOutsideEntityChart1 varchar(4000)=null,                              
@trusteeFamilyMemberChart2 varchar(4000)=null,                              
                               
@sortColumn varchar(150)='name',                               
@sortOrder bit=1,-->Asc 0-->Descending                        
@selectedChartXml text=null                                   
)                                              
AS BEGIN                                              
                                              
SET NOCOUNT ON                                              
                                  
if(@firstName is null)                                              
 Set @firstName='%'                                              
else                                              
 Set @firstName=@firstName+'%'                                               
                                              
if(@lastName is null)                                              
 Set @lastName='%'                                              
else                                              
 Set @lastName=@lastName+'%'                                               
            
if(@startDate is null)                                            
  Select @startDate=cast('1900-01-01' as datetime)                
                                  
if(@endDate is null)                                
 Set @endDate= GetDate()           
          
declare @roleTable table (Sequence int identity, COIRole varchar(75)) 
declare @chartsTable table(Sequence   int identity,chart varchar(75))                                           
                      
-------Get the COIIDs matching the preliminary search parameters(i.e firstname,lastname,username,date,status etc)-----          
--This step will narrow down the no.of records on which the advanced search(determined by the passed in parameters) will be performed-----                           
Declare @temp as table(coiId int)       
if @username is null       
 BEGIN                                
    Insert into @temp SELECT Max(COI.[Id]) as COIID                                
   from AdmPerson adp                         
   INNER JOIN ConflictOfInterest coi                                    
    On   adp.[LastName] like @lastName                  
    AND adp.[FirstName] like @firstName                                     
    AND coi.[admPersonId]=adp.[Id]            
    AND (DateDiff(day,@startDate ,coi.[datesubmitted])  >= 0)                   
    AND (DateDiff(day,@endDate,coi.[datesubmitted])  <= 0)
    AND [Status]in ('COISTATUS_SUBMITTED','COISTATUS_REVIEWED')                                                 
  INNER JOIN COI_QuestionnaireAnswer_map cqm                                    
    On cqm.COIID = coi.[Id]                                 
   INNER JOIN QuestionnaireAnswer qa                                    
    On qa.[Id]=cqm.[QuestionnaireAnswerId]                  
  where                                          
    (isnull(cast(coi.[HasOutsideActivities] as varchar(1)),'*') like Case @nothingToDisclose                                              
     When 0 then '%'                                      
     else                                       
     '0'                                      
     END                            
   and  isnull(cast(coi.[HasEquityInterests] as varchar(1)),'*')like Case @nothingToDisclose                                              
     When 0 then '%'                                      
     else                                       
     '0'                                      
     END                             
   and isnull(cast(coi.[HasFamilyAssociations]as varchar(1)),'*') like Case @nothingToDisclose                                              
     When 0 then '%'                                      
     else                                       
     '0'                                      
     END 
   and ISNULL(cast(coi.[Royalty] as varchar(1)),'*') like Case @nothingToDisclose                                  
     When 0 then '%'                         
     else                           
     '0'                          
     END)                              
  group by adp.[ID]                                
  Order by adp.[Id]        
END      
ELSE      
 BEGIN      
 Set @userName=@userName+'%'      
   Insert into @temp SELECT  Max(COI.[Id]) as COIID                                
   from AdmPerson adp                         
   INNER JOIN ConflictOfInterest coi                                    
    On   adp.[LastName] like @lastName                  
    AND adp.[FirstName] like @firstName                                     
    AND coi.[admPersonId]=adp.[Id]            
    AND (DateDiff(day,@startDate ,coi.[datesubmitted])  >= 0)                   
    AND (DateDiff(day,@endDate,coi.[datesubmitted])  <= 0)                                  
    AND [Status]in ('COISTATUS_SUBMITTED','COISTATUS_REVIEWED')
   LEFT OUTER JOIN FwkDomainUser fdu                         
    On adp.[FwkDomainUserId]=fdu.[Id]                                 
   INNER JOIN COI_QuestionnaireAnswer_map cqm                 
    On cqm.COIID = coi.[Id]                    
   INNER JOIN QuestionnaireAnswer qa                                    
    On qa.[Id]=cqm.[QuestionnaireAnswerId]                  
    AND fdu.UserName like @userName                  
   where                                          
    (isnull(cast(coi.[HasOutsideActivities] as varchar(1)),'*') like Case @nothingToDisclose                                              
     When 0 then '%'                                      
     else                                       
     '0'                                      
     END                            
   and  isnull(cast(coi.[HasEquityInterests] as varchar(1)),'*')like Case @nothingToDisclose                                              
     When 0 then '%'                                      
     else                                       
     '0'                                      
     END                             
   and isnull(cast(coi.[HasFamilyAssociations]as varchar(1)),'*') like Case @nothingToDisclose                                              
     When 0 then '%'                                      
     else                                       
     '0'                                      
     END 
   and ISNULL(cast(coi.[Royalty] as varchar(1)),'*') like Case @nothingToDisclose                                  
     When 0 then '%'                         
     else                           
     '0'                          
     END)                               
                                      
  group by adp.[ID]                                
  Order by adp.[Id]      
 END                              
                          
-------------------------------------------Operational Logic----------------------------------------          
/*          
Using the COIIds obtained from the preliminary search,apply the advanced search(parameters passed in to further filter the results)          
Sorting will be applied on the final records yielded           
*/          
                        
Declare @tempMatchedRecords  as table(refIndex int ,cOIId int)                              
Declare @refIndex as int                        
Set @refIndex=1                             
insert into @tempMatchedRecords Select @refIndex,COIId from @temp           
          
                       
--*********Load the seleted charts in to a temporary table that will be joined for answers*****--------                    
if @selectedChartXml is not null                    
 BEGIN                    
  declare @xmlchartDoc int                               
  exec sp_xml_preparedocument @xmlchartDoc output, @selectedChartXml                                    
  insert into @chartsTable select [Col1] from openxml(@xmlchartDoc, '//Table1',2) with (Col1 varchar(75))                          
  exec sp_xml_removedocument @xmlchartDoc                     
 END  
---------*******************COI Role search(Common Section of repots)************************---------------                           
if(@roleXml is not null )                        
Begin           
declare @xmlRoleDoc int                           
 exec sp_xml_preparedocument @xmlRoleDoc output, @roleXml                                    
 insert into @roleTable select [Col1] from openxml(@xmlRoleDoc, '//Table1',2) with (Col1 varchar(75))                                             
 exec sp_xml_removedocument @xmlRoleDoc                        
                         
 Set @refIndex=@refIndex+1                                             
  insert into @tempMatchedRecords           
   Select distinct @refIndex, t.COIId  from @temp t          
    inner join conflictOfinterest coi          
    On coi.[Id]=t.CoiId          
    inner join admperson adp          
    on coi.[admpersonId]=adp.[Id]          
    inner join @roleTable rt          
    on rt.[COIRole]=adp.[COIRole]          
                         
 if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                        
Return                        
END                         
                    
Declare @tempMatchedCharts  as table(cOIId int,qName varchar(150),ansName varchar(256),ansValue varchar(4000))    
---------****************Select COIs containing only those charts selected in the report**************-----------    
if @selectedChartXml is not null                        
    BEGIN      
  insert into @tempMatchedCharts                   
     Select distinct t.COIId ,q.[Name],a.[Name],a.[Value] from         
    (Select temp.COIId,QuestionnaireAnswerId  from COI_QuestionnaireAnswer_map chm         
     inner join @temp temp         
      On temp.COIID is not null         
      and chm.COIId =temp.COIID         
      and chm.QuestionnaireAnswerID is not null) t         
     inner join Answer a                   
    On t.questionnaireAnswerID=a.QuestionnaireAnswerID                           
      Inner Join Questionnaireanswer qa                        
    On qa.[Id]=t.questionnaireAnswerID                        
      Inner Join QuestionnaireVersion qv                         
    On qv.[Id]=qa.[QuestionnaireVersionId]                        
      Inner Join Questionnaire q                     
    On q.[Id]=qv.[QuestionnaireId]    
    and t.COIID is not null    
    
 if exists(Select chart from @chartsTable where chart='chart1')      
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your outside activities'    
   and t.COIID is not null     
   if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return         
  END     
 if exists(Select chart from @chartsTable where chart='chart2')      
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your equity interests'    
   and t.COIID is not null    
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return          
  END     
 if exists(Select chart from @chartsTable where chart='chart3')      
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your family''s associations and equity interests'    
   and t.COIID is not null    
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return          
  END
  --Added chart4 block
  if exists(Select chart from @chartsTable where chart='chart4')      
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your Intellectual Property'    
   and t.COIID is not null    
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return          
  END
  -- End of chart 4 block        
  if exists(Select chart from @chartsTable where chart='trusteechart1')    
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='trustee association'    
    and t.COIID is not null    
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return      
  END    
 if exists(Select chart from @chartsTable where chart='trusteechart2')    
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='trustee''s family associations and equity interests'    
   and t.COIID is not null     
   if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return     
  END        
 END         
    
---------*******************Outside entity search(can be performed specific to charts as well as across charts)*****************---------------                                                                    
if(@outSideEntityName is not null )                        
BEGIN                                              
                 
 Set @outSideEntityName=@outSideEntityName+'%'     
if exists (Select 1 from @chartsTable)    
 BEGIN      
 if exists(Select chart from @chartsTable where chart='chart1')      
  BEGIN    
   Set @refIndex=@refIndex+1   
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your outside activities'    
   and (isNULL(t.[ansValue],'*') Like @outSideEntityName and (t.[ansName]='outsideentityname'))                      
      and t.COIID is not null     
   if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return         
  END     
 if exists(Select chart from @chartsTable where chart='chart2')      
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your equity interests'    
   and (isNULL(t.[ansValue],'*') Like @outSideEntityName and (t.[ansName]='outsideentityname'))                      
      and t.COIID is not null    
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return          
  END     
 if exists(Select chart from @chartsTable where chart='chart3')      
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your family''s associations and equity interests'    
   and (isNULL(t.[ansValue],'*') Like @outSideEntityName and (t.[ansName]='outsideentityname'))                      
      and t.COIID is not null    
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return          
  END 
 --Added Chart 4 Block
 if exists(Select chart from @chartsTable where chart='chart4')      
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your Intellectual Property'    
   and (isNULL(t.[ansValue],'*') Like @outSideEntityName and (t.[ansName] in ('EntityPaysYou','EntityPaysPHS')))                      
      and t.COIID is not null    
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return          
  END 
--End of Chart 4 Block       
  if exists(Select chart from @chartsTable where chart='trusteechart1')    
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='trustee association'    
   and (isNULL(t.[ansValue],'*') Like @outSideEntityName and (t.[ansName]='outsideentityname'))                      
      and t.COIID is not null    
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return      
  END    
 if exists(Select chart from @chartsTable where chart='trusteechart2')    
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='trustee''s family associations and equity interests'    
   and (isNULL(t.[ansValue],'*') Like @outSideEntityName and (t.[ansName]='outsideentityname'))                      
      and t.COIID is not null     
   if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return     
  END     
 END    
ELSE    
 BEGIN    
  Set @refIndex=@refIndex+1    
  insert into @tempMatchedRecords             
     Select distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm             
   inner join @temp temp             
    On chm.COIId =temp.COIID             
    and chm.QuestionnaireAnswerID is not null             
   inner join answer a on chm.questionnaireAnswerID=a.QuestionnaireAnswerID              
    and (isNULL(a.[Value],'*') Like @outSideEntityName and (a.[Name]='outsideentityname'))     
 END        
END                      
---------*******************Business nature search(can be performed specific to charts as well as across charts)*****************---------------                                                                    
                              
if(@businessNature is not null )                        
 BEGIN    
 if exists (Select 1 from @chartsTable)    
 BEGIN             
  if exists(Select chart from @chartsTable where chart='chart1')      
   BEGIN    
    Set @refIndex=@refIndex+1    
    insert into @tempMatchedRecords     
    Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your outside activities'    
    and (isNULL(t.[ansValue],'*') Like @businessNature and (t.[ansName]='briefdescriptionofbusiness'))                      
    and t.COIID is not null     
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
    Return         
   END     
  if exists(Select chart from @chartsTable where chart='chart2')      
   BEGIN    
    Set @refIndex=@refIndex+1    
    insert into @tempMatchedRecords     
    Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your equity interests'    
    and (isNULL(t.[ansValue],'*') Like @businessNature and (t.[ansName]='briefdescriptionofbusiness'))                      
    and t.COIID is not null    
     if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
    Return          
   END     
  if exists(Select chart from @chartsTable where chart='chart3')      
   BEGIN    
    Set @refIndex=@refIndex+1    
    insert into @tempMatchedRecords     
    Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your family''s associations and equity interests'    
    and (isNULL(t.[ansValue],'*') Like @businessNature and (t.[ansName]='briefdescriptionofbusiness'))                      
    and t.COIID is not null    
     if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
    Return          
   END 
   --Added Chart 4 block
   if exists(Select chart from @chartsTable where chart='chart4')      
   BEGIN    
    Set @refIndex=@refIndex+1    
    insert into @tempMatchedRecords     
    Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your Intellectual Property'    
    and (isNULL(t.[ansValue],'*') Like @businessNature and (t.[ansName]='briefdescriptionofbusiness'))                      
    and t.COIID is not null    
     if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
    Return          
   END 
   --End of chart 4 block    
   if exists(Select chart from @chartsTable where chart='trusteechart1')    
   BEGIN    
    Set @refIndex=@refIndex+1    
    insert into @tempMatchedRecords     
    Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='trustee association'    
    and (isNULL(t.[ansValue],'*') Like @businessNature and (t.[ansName]='briefdescriptionofbusiness'))                      
    and t.COIID is not null    
     if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
    Return      
   END    
  if exists(Select chart from @chartsTable where chart='trusteechart2')    
   BEGIN    
    Set @refIndex=@refIndex+1    
    insert into @tempMatchedRecords     
    Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='trustee''s family associations and equity interests'    
    and (isNULL(t.[ansValue],'*') Like @businessNature and (t.[ansName]='briefdescriptionofbusiness'))                      
    and t.COIID is not null     
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
    Return     
   END     
 END    
ELSE    
 BEGIN    
  Set @refIndex=@refIndex+1    
  insert into @tempMatchedRecords             
     Select distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm             
   inner join @temp temp             
    On chm.COIId =temp.COIID             
    and chm.QuestionnaireAnswerID is not null             
   inner join answer a on chm.questionnaireAnswerID=a.QuestionnaireAnswerID              
    and (isNULL(a.[Value],'*') Like @businessNature and (a.[Name]='briefdescriptionofbusiness'))     
 END       
END                      
                          
---------*******************Company Type Search(can be performed specific to charts as well as across charts)*****************---------------                                                                                         
if(@companyType is not null )                          
BEGIN      
if exists (Select 1 from @chartsTable)    
 BEGIN      
 if exists(Select chart from @chartsTable where chart='chart1')      
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your outside activities'    
   and (isNULL(t.[ansValue],'*') Like @companyType and (t.[ansName]='public'))                      
      and t.COIID is not null     
   if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return         
  END     
 if exists(Select chart from @chartsTable where chart='chart2')      
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your equity interests'    
   and (isNULL(t.[ansValue],'*') Like @companyType and (t.[ansName]='public'))                      
      and t.COIID is not null    
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return          
  END     
 if exists(Select chart from @chartsTable where chart='chart3')      
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your family''s associations and equity interests'    
   and (isNULL(t.[ansValue],'*') Like @companyType and (t.[ansName]='public'))                      
      and t.COIID is not null    
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return          
  END
  --Added Chart 4 block
  --No company type question available in chart 4
  --End of Chart 4 block     
  if exists(Select chart from @chartsTable where chart='trusteechart1')    
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='trustee association'    
   and (isNULL(t.[ansValue],'*') Like @companyType and (t.[ansName]='public'))                      
      and t.COIID is not null    
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return      
  END    
 if exists(Select chart from @chartsTable where chart='trusteechart2')    
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='trustee''s family associations and equity interests'    
   and (isNULL(t.[ansValue],'*') Like @companyType and (t.[ansName]='public'))                      
      and t.COIID is not null     
   if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return     
  END    
 END      
ELSE    
 BEGIN    
  Set @refIndex=@refIndex+1    
  insert into @tempMatchedRecords             
     Select distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm             
   inner join @temp temp             
    On chm.COIId =temp.COIID             
    and chm.QuestionnaireAnswerID is not null             
   inner join answer a on chm.questionnaireAnswerID=a.QuestionnaireAnswerID              
    and (isNULL(a.[Value],'*') Like @companyType and (a.[Name]='public'))     
 END                
END                
                            
---------******Percentage total equity owned Search(can be performed specific to charts as well as across charts)*****************---------------                                                                                    
if(@percentageTotalEquityOwned is not null)                        
BEGIN           
if exists (Select 1 from @chartsTable)    
 BEGIN    
 if exists(Select chart from @chartsTable where chart='chart1')      
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your outside activities'    
   and (isNULL(t.[ansValue],'*') Like @percentageTotalEquityOwned and (t.[ansName]='totalpercentequityowned'))                      
      and t.COIID is not null     
   if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return         
  END     
 if exists(Select chart from @chartsTable where chart='chart2')      
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your equity interests'    
   and (isNULL(t.[ansValue],'*') Like @percentageTotalEquityOwned and (t.[ansName]='totalpercentequityowned'))                      
      and t.COIID is not null    
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return          
  END     
 if exists(Select chart from @chartsTable where chart='chart3')      
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your family''s associations and equity interests'    
   and (isNULL(t.[ansValue],'*') Like @percentageTotalEquityOwned and (t.[ansName]='totalpercentequityowned'))                      
      and t.COIID is not null    
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return          
  END
  --Added Chart 4 block
	--No percentage equity owned available in chart 4
  --End of chart 4 block     
  if exists(Select chart from @chartsTable where chart='trusteechart1')    
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='trustee association'    
   and (isNULL(t.[ansValue],'*') Like @percentageTotalEquityOwned and (t.[ansName]='totalpercentequityowned'))                      
      and t.COIID is not null    
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return      
  END    
 if exists(Select chart from @chartsTable where chart='trusteechart2')    
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='trustee''s family associations and equity interests'    
   and (isNULL(t.[ansValue],'*') Like @percentageTotalEquityOwned and (t.[ansName]='totalpercentequityowned'))                      
      and t.COIID is not null     
   if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return     
  END     
 END       
ELSE    
 BEGIN    
  Set @refIndex=@refIndex+1    
  insert into @tempMatchedRecords             
     Select distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm             
   inner join @temp temp             
    On chm.COIId =temp.COIID             
    and chm.QuestionnaireAnswerID is not null             
   inner join answer a on chm.questionnaireAnswerID=a.QuestionnaireAnswerID              
    and (isNULL(a.[Value],'*') Like @percentageTotalEquityOwned and (a.[Name]='totalpercentequityowned'))     
 END            
END                     
---------*******************Service type search(Search on chart1)*****************---------------                                                                                         
if(@chart1ServiceProvided is not null )                        
BEGIN                                              
 Set @refIndex=@refIndex+1                              
  insert into @tempMatchedRecords           
    Select distinct @refIndex,chm.COIId from           
     COI_QuestionnaireAnswer_map chm           
     inner join @temp temp           
      On chm.COIId =temp.COIID and chm.QuestionnaireAnswerID is not null           
     inner join   answer a           
      on chm.questionnaireAnswerID=a.QuestionnaireAnswerID           
      and (isNULL(a.[Value],'*') Like @chart1ServiceProvided           
      and (a.[Name]='descrserviceprovide'))                        
 if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                          
Return                        
END                                 
---------*******************Annual equity compensation search(Search on chart1)*****************---------------                                                                                                               
if(@chart1AnnualEquityCompensation is not null )                        
BEGIN                                              
 Set @refIndex=@refIndex+1                              
  insert into @tempMatchedRecords           
    Select distinct @refIndex,t.COIId  from           
    (Select COIId,QuestionnaireAnswerId  from COI_QuestionnaireAnswer_map where COIId in (Select COIId from @temp) ) t            
    INNER join Answer a                                 
     on t.questionnaireAnswerID=a.QuestionnaireAnswerID           
      and (isNULL(a.[Value],'*') Like @chart1AnnualEquityCompensation and (a.[Name]='annualequity'))                        
 if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                          
 Return                        
END                                 
---------*******************Fiduciary position search(Search on chart1)*****************---------------                                                                                                               
if(@chart1FiduciaryPosition is not null )                        
BEGIN                                              
 Set @refIndex=@refIndex+1                              
  insert into @tempMatchedRecords           
    Select distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm           
     inner join @temp temp           
      On chm.COIId =temp.COIID and chm.QuestionnaireAnswerID is not null           
     inner join   answer a           
      on chm.questionnaireAnswerID=a.QuestionnaireAnswerID            
      and (isNULL(a.[Value],'*') Like @chart1FiduciaryPosition and (a.[Name]='position'))                         
 if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                        
 Return                        
END                                
--     
---------*******************Fiduciary position search(Search on chart1)*****************---------------                                                                                                               
if(@chart1CSRLReview is not null )                        
BEGIN                                              
 Set @refIndex=@refIndex+1                              
  insert into @tempMatchedRecords           
    Select distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm           
     inner join @temp temp           
      On chm.COIId =temp.COIID and chm.QuestionnaireAnswerID is not null           
     inner join   answer a           
      on chm.questionnaireAnswerID=a.QuestionnaireAnswerID            
      and (isNULL(a.[Value],'*') Like @chart1CSRLReview and (a.[Name]='CSRL'))                         
 if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                        
 Return                        
END                             
---------*******************aggregate or equity owned search(Search on chart1)*****************---------------                                                                                                               
if ((@chart1IsAggregate =1) and (@chart1ValueOfEquityOwned is not null))                            
 Begin                            
  Set @refIndex=@refIndex+1                            
   insert into @tempMatchedRecords           
     Select distinct @refIndex ,t.COIId  from           
      (Select COIId,QuestionnaireAnswerId from COI_QuestionnaireAnswer_map           
        where COIId in (Select COIId from @temp) ) t            
      INNER join conflictOfInterest coi                           
       On coi.[Id]=t.COIId                            
       AND isNULL(cast(coi.[aggregateEquity] as varchar(15)),'*') = @chart1ValueOfEquityOwned                               
 END                            
ELSE If(@chart1ValueOfEquityOwned is not null )                            
  BEGIN                                              
  Set @refIndex=@refIndex+1                              
   insert into @tempMatchedRecords           
     Select distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm           
      inner join @temp temp           
       On chm.COIId =temp.COIID           
       and chm.QuestionnaireAnswerID is not null           
      inner join answer a on chm.questionnaireAnswerID=a.QuestionnaireAnswerID            
       and (isNULL(a.[Value],'*') Like @chart1ValueOfEquityOwned and (a.[Name]='totalequityowned'))                            
  END                         
if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                        
Return                        
                     
---------*******************written agreement search(Search on chart1)*****************---------------                        
if(@chart1WrittenAgreement is not null )                         
BEGIN                                             
 Set @refIndex=@refIndex+1                              
  insert into @tempMatchedRecords          
    Select distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm           
  inner join @temp temp           
    On chm.COIId =temp.COIID and chm.QuestionnaireAnswerID is not null           
  inner join   answer a           
    on chm.questionnaireAnswerID=a.QuestionnaireAnswerID            
    and (isNULL(a.[Value],'*') Like @chart1WrittenAgreement and (a.[Name]='writtenagreement'))                          
                         
 if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                          
 Return                        
END                                
---------*******************Days of outside activity search(Search on chart1)*****************---------------                                                                                                               
if(@chart1DaysOfOutsideActivity is not null )                        
BEGIN                                              
Set @refIndex=@refIndex+1                              
  insert into @tempMatchedRecords           
    Select  distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm           
     inner join @temp temp           
      On chm.COIId =temp.COIID and chm.QuestionnaireAnswerID is not null           
     inner join   answer a           
      on chm.questionnaireAnswerID=a.QuestionnaireAnswerID            
      and                                            
      (cast(isNULL(a.[Value],0)as float) >= cast(@chart1DaysOfOutsideActivity as float) and (a.[Name]='timespent'))                    
          
 if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                         
 Return                        
END        
---------*******************annual cash compensation search(Search on chart1)*****************---------------                                                                                                               
if(@chart1AnnualCashCompensation is not null )                         
BEGIN                                             
 Set @refIndex=@refIndex+1                              
  insert into @tempMatchedRecords           
    Select distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm           
     inner join @temp temp           
      On chm.COIId =temp.COIID           
      and chm.QuestionnaireAnswerID is not null           
     inner join answer a           
      on chm.questionnaireAnswerID=a.QuestionnaireAnswerID            
      and                                            
      (isNULL(a.[Value],'*') Like cast(@chart1AnnualCashCompensation as varchar(30))and (a.[Name]='annualcashcomp'))                               
 if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                        
 Return                        
END                        
                           
---------*******************value of equity interest search(Search on chart2)*****************---------------                                                                                                               
if(@chart2ValueOfEquityInterest is not null )                        
BEGIN                                              
 Set @refIndex=@refIndex+1                             
  insert into @tempMatchedRecords           
   Select  distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm           
    inner join @temp temp           
     On chm.COIId =temp.COIID           
     and chm.QuestionnaireAnswerID is not null           
    inner join answer a           
     on chm.questionnaireAnswerID=a.QuestionnaireAnswerID           
     and (isNULL(a.[Value],'*') Like cast(@chart2ValueOfEquityInterest as varchar(30))and (a.[Name]='valueofequity'))                              
 if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                          
 Return                        
END                        
---------*****Family member search(Search on chart3(Individual) and chart2(Trustee))*****************---------------                                    
              
if(@chart3FamilyMember is not null or @trusteeFamilyMemberChart2 is not null )                         
BEGIN          
if exists(Select chart from @chartsTable where chart='chart1')      
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your outside activities'    
   AND ((isNULL(t.[ansValue],'*') Like cast(@chart3FamilyMember as varchar(30))and (t.[ansName]='familymembertype'))or                    
   (isNULL(t.[ansValue],'*') Like cast(@trusteeFamilyMemberChart2 as varchar(30))and (t.[ansName]='familymembertype')))                       
      and t.COIID is not null     
   if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return         
  END     
 if exists(Select chart from @chartsTable where chart='chart2')      
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your equity interests'    
   AND ((isNULL(t.[ansValue],'*') Like cast(@chart3FamilyMember as varchar(30))and (t.[ansName]='familymembertype'))or                    
   (isNULL(t.[ansValue],'*') Like cast(@trusteeFamilyMemberChart2 as varchar(30))and (t.[ansName]='familymembertype')))                       
      and t.COIID is not null    
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return          
  END     
 if exists(Select chart from @chartsTable where chart='chart3')      
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='your family''s associations and equity interests'    
   AND ((isNULL(t.[ansValue],'*') Like cast(@chart3FamilyMember as varchar(30))and (t.[ansName]='familymembertype'))or                    
   (isNULL(t.[ansValue],'*') Like cast(@trusteeFamilyMemberChart2 as varchar(30))and (t.[ansName]='familymembertype')))                       
      and t.COIID is not null    
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return          
  END     
  if exists(Select chart from @chartsTable where chart='trusteechart1')    
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='trustee association'    
   AND ((isNULL(t.[ansValue],'*') Like cast(@chart3FamilyMember as varchar(30))and (t.[ansName]='familymembertype'))or                    
   (isNULL(t.[ansValue],'*') Like cast(@trusteeFamilyMemberChart2 as varchar(30))and (t.[ansName]='familymembertype')))                       
      and t.COIID is not null    
    if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return      
  END    
 if exists(Select chart from @chartsTable where chart='trusteechart2')    
  BEGIN    
   Set @refIndex=@refIndex+1    
   insert into @tempMatchedRecords     
   Select distinct @refIndex, t.COIId from @tempMatchedCharts t where t.qname='trustee''s family associations and equity interests'    
         AND ((isNULL(t.[ansValue],'*') Like cast(@chart3FamilyMember as varchar(30))and (t.[ansName]='familymembertype'))or                    
   (isNULL(t.[ansValue],'*') Like cast(@trusteeFamilyMemberChart2 as varchar(30))and (t.[ansName]='familymembertype')))                       
      and t.COIID is not null     
   if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                     
   Return     
  END    
END                        
                         
---------*******************Family member association search(Search on chart3)*****************---------------                                                                                                               
if(@chart3FamilyMemberAssociation is not null )                        
BEGIN                                              
 Set @refIndex=@refIndex+1                              
  insert into @tempMatchedRecords           
    Select distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm           
     inner join @temp temp           
      On chm.COIId =temp.COIID and chm.QuestionnaireAnswerID is not null           
     inner join   answer a           
      on chm.questionnaireAnswerID=a.QuestionnaireAnswerID            
      and                                           
      (isNULL(a.[Value],'*') Like cast(@chart3FamilyMemberAssociation as varchar(30))and (a.[Name]='familymemberrelationship'))                               
if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                        
Return                        
END                        
---------**********value of equity interest search(Search on chart3)*****************---------------                                                                                                               
if(@chart3ValueOfEquityInterest is not null )                        
BEGIN                                              
Set @refIndex=@refIndex+1                              
   insert into @tempMatchedRecords           
    Select distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm          
      inner join @temp temp           
      On chm.COIId =temp.COIID           
      and chm.QuestionnaireAnswerID is not null           
     inner join answer a           
      on chm.questionnaireAnswerID=a.QuestionnaireAnswerID           
      and                                            
      (isNULL(a.[Value],'*') Like cast(@chart3ValueOfEquityInterest as varchar(30))          
      and (a.[Name]='valueofequity'))                              
if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                         
Return                        
END

---------**********is Payed by partners(Search on chart4)*****************---------------                                                                                                               
if(@chart4isPayedBypartners  is not null )                        
BEGIN                                              
Set @refIndex=@refIndex+1                              
   insert into @tempMatchedRecords           
    Select distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm          
      inner join @temp temp           
      On chm.COIId =temp.COIID           
      and chm.QuestionnaireAnswerID is not null           
     inner join answer a           
      on chm.questionnaireAnswerID=a.QuestionnaireAnswerID           
      and                                            
      (isNULL(a.[Value],'*') Like cast(@chart4isPayedBypartners  as varchar(max))          
      and (a.[Name]='Payment'))                              
if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                         
Return                        
END    
---------**********Payment Nature (Search on chart4)*****************---------------                                                                                                               
if(@chart4paymentNature is not null )                        
BEGIN                                              
Set @refIndex=@refIndex+1                              
   insert into @tempMatchedRecords           
    Select distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm          
      inner join @temp temp           
      On chm.COIId =temp.COIID           
      and chm.QuestionnaireAnswerID is not null           
     inner join answer a           
      on chm.questionnaireAnswerID=a.QuestionnaireAnswerID           
      and                                            
      (isNULL(a.[Value],'*') Like cast(@chart4paymentNature  as varchar(max))          
      and (a.[Name] in ('NaturePayments','OtherNature')))                              
if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                         
Return                        
END 
---------**********Payment Purpose (Search on chart4)*****************---------------                                                                                                               
if(@chart4paymentPurpose is not null )                        
BEGIN                                              
Set @refIndex=@refIndex+1                              
   insert into @tempMatchedRecords           
    Select distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm          
      inner join @temp temp           
      On chm.COIId =temp.COIID           
      and chm.QuestionnaireAnswerID is not null           
     inner join answer a           
      on chm.questionnaireAnswerID=a.QuestionnaireAnswerID           
      and                                            
      (isNULL(a.[Value],'*') Like cast(@chart4paymentPurpose   as varchar(max))          
      and (a.[Name] in ('PurposePayments','OtherPurpose')))                             
if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                         
Return                        
END 

---------**********Payment Purpose (Search on chart4)*****************---------------                                                                                                               
if(@chart4annualPayment  is not null )                        
BEGIN                                              
Set @refIndex=@refIndex+1                              
   insert into @tempMatchedRecords           
    Select distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm          
      inner join @temp temp           
      On chm.COIId =temp.COIID           
      and chm.QuestionnaireAnswerID is not null           
     inner join answer a           
      on chm.questionnaireAnswerID=a.QuestionnaireAnswerID           
      and                                            
      (isNULL(a.[Value],'*') Like cast(@chart4annualPayment    as varchar(max))          
      and (a.[Name]='AnnualPayment'))                              
if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                         
Return                        
END 
                        
---------*******************Non partners association search(Search on chart1(trustee))*****************---------------                                                                                                               
if(@trusteeNonPartnersAssociationChart1 is not null )                        
BEGIN                        
 Set @refIndex=@refIndex+1                              
  insert into @tempMatchedRecords           
    Select distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm           
     inner join @temp temp           
      On chm.COIId =temp.COIID           
      and chm.QuestionnaireAnswerID is not null           
     inner join   answer a           
      on chm.questionnaireAnswerID=a.QuestionnaireAnswerID           
      and                                           
      (isNULL(a.[Value],'*') Like @trusteeNonPartnersAssociationChart1           
      and (a.[Name]='presentassociation'))                      
          
 if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                         
 Return                        
END               
 ---------*******************description of outside entity search(Search on chart1(trustee))*****************---------------                                                                                                               
if(@trusteeDescOfOutsideEntityChart1 is not null )                         
BEGIN                                             
 Set @refIndex=@refIndex+1                              
  insert into @tempMatchedRecords           
    Select distinct @refIndex,chm.COIId from COI_QuestionnaireAnswer_map chm           
     inner join @temp temp           
      On chm.COIId =temp.COIID           
      and chm.QuestionnaireAnswerID is not null           
     inner join   answer a           
      on chm.questionnaireAnswerID=a.QuestionnaireAnswerID            
      and                                            
      (isNULL(a.[Value],'*') Like @trusteeDescOfOutsideEntityChart1           
      and (a.[Name]='briefdescriptionofbusiness'))                              
 if not exists(Select 1 from @tempMatchedRecords where refIndex=@refIndex)                          
Return            
END                             
                       
-------------------Sort the results sortcolumn(parameter)-------------------------------------                              
                  
  Select  @refIndex= sum(Distinct (refIndex)) from @tempMatchedRecords where coiID is not null                            
                    
  if @SortColumn ='name'or @SortColumn='title' or @SortColumn='lastname' or @SortColumn  ='supervisor'                            
   Begin 
	Select T.COIid,coi.admpersonId as [Id],adp1.[Id]as SupervisorId from           
   (Select  COIId,Sum(Distinct(refIndex))as RefIndexSum from @tempMatchedRecords group by COIID)T                              
   inner join ConflictOfInterest coi                              
    On COI.Id=T.COIid                            
   Left Join admPerson adp                            
    On adp.ID=Coi.admpersonId                
   Left outer Join COI_Hierarchy_Map chm                
    On chm.ChildId=Adp.[Id]                
    and chm.Removed=0                         
   Left Outer Join AdmPerson adp1                         
    on adp1.[Id]=chm.parentId                                                                
    Where T.RefIndexSum=@refIndex                              
    AND T.COIid is not null                   
   Order by                             
    CASE @sortOrder                                      
      WHEN 0 THEN               
       Case  @SortColumn                             
     When 'name' then adp.LastName                           
     When 'title' then adp.Title                          
     WHEN 'lastname' then adp.LastName                          
     WHEN 'supervisor' then adp1.LastName                            
     Else adp.LastName                             
       END              
    END DESC,              
      CASE @sortOrder                                      
      WHEN 1 then                            
       Case  @SortColumn                             
     When 'name' then adp.LastName                           
     When 'title' then adp.Title                            
     WHEN 'lastname' then adp.LastName                            
     WHEN 'supervisor' then adp1.LastName                            
     Else adp.LastName                           
       END                                     
       END ,              
      Case @sortOrder              
       When 0 then              
     case @SortColumn              
     When 'name' then adp.FirstName                          
     WHEN 'lastname' then adp.FirstName                          
     WHEN 'supervisor' then adp1.FirstName               
     END              
       END DESC,              
      Case @sortOrder              
       When 1 then              
     case @SortColumn              
     When 'name' then adp.FirstName                          
     WHEN 'lastname' then adp.FirstName                          
     WHEN 'supervisor' then adp1.FirstName               
     END              
       END              
                                
 END                              
   ELSE IF @SortColumn='policyviolation' or @SortColumn='equityinterest'or @SortColumn='familyinterest'or @SortColumn='outsideactivity'or @SortColumn='otheractivity' or @SortColumn = 'Royalty'                           
 Begin                            
  Select T.COIid,coi.admpersonId as [Id],adp1.[Id]as SupervisorId  
 from (Select COIId,Sum(Distinct(refIndex))as RefIndexSum from @tempMatchedRecords group by COIID)T                              
   inner join ConflictOfInterest coi                              
    On COI.Id=T.COIid                          
   Left Join admPerson adp                            
    On adp.ID=Coi.admpersonId  
   Left outer Join COI_Hierarchy_Map chm                
    On chm.ChildId=adp.[Id]                
    and chm.Removed=0                         
   Left Outer Join AdmPerson adp1                         
    on adp1.[Id]=chm.parentId                            
    Where T.RefIndexSum=@refIndex                              
    AND T.COIid is not null                            
    Order by                             
    CASE @sortOrder                                      
      WHEN 1 THEN                                       
       CASE @sortColumn                                      
     WHEN 'policyviolation' THEN coi.PolicyViolation                                      
     WHEN 'equityinterest' THEN coi.HasEquityInterests                                  
     WHEN 'familyinterest' THEN coi.HasFamilyAssociations                                  
     WHEN 'outsideactivity' THEN coi.HasOutsideActivities                                  
     WHEN 'otheractivity' THEN coi.OtherActivity
	 WHEN 'Royalty' THEN coi.Royalty                                            
     ELSE coi.PolicyViolation                                      
     END                                      
       END                                                   
       DESC,                                      
       CASE @sortOrder                                      
      WHEN 0 THEN                                       
       CASE @sortColumn                                      
     WHEN 'policyviolation' THEN coi.PolicyViolation                                      
     WHEN 'equityinterest' THEN coi.HasEquityInterests                                                                          
     WHEN 'familyinterest' THEN coi.HasFamilyAssociations                                      
     WHEN 'outsideactivity' THEN coi.HasOutsideActivities                                      
     WHEN 'otheractivity' THEN coi.OtherActivity
	 WHEN 'Royalty' THEN coi.Royalty                       
     ELSE coi.PolicyViolation       
       END                                      
       END                               
   END                            
  ELSE IF @SortColumn='datesubmitted'                            
   Begin                            
   Select T.COIid,coi.admpersonId as [Id],adp1.[Id]as SupervisorId--adp.[LastName]   
 from (Select COIId,Sum(Distinct(refIndex))as RefIndexSum from @tempMatchedRecords group by COIID)T                              
    inner join ConflictOfInterest coi                              
     On COI.Id=T.COIid                            
    Left Join admPerson adp                            
     On adp.ID=Coi.admpersonId  
 Left outer Join COI_Hierarchy_Map chm                
    On chm.ChildId=adp.[Id]                
    and chm.Removed=0                         
   Left Outer Join AdmPerson adp1                         
    on adp1.[Id]=chm.parentId                                
     Where T.RefIndexSum=@refIndex                              
     AND T.COIid is not null                            
    Order by                             
     CASE @sortOrder                                      
       WHEN 1 THEN                                       
        CASE @sortColumn                                      
      WHEN 'datesubmitted' THEN coi.DateSubmitted                                   
      END                                      
        END                                                   
        DESC,                                      
       CASE @sortOrder                                      
       WHEN 0 THEN                                       
        CASE @sortColumn                                      
       WHEN 'datesubmitted' THEN coi.DateSubmitted                                   
       END                            
        END                               
      END                 
 END           



